using System;
using System.Data;
using istIUniDB;
using System.Text;
using System.Xml;
using System.IO;
using System.Globalization;
using Shraga.BLL;

namespace Shraga.Client
{
	public class InternetDataTransfer
	{
		public InternetDataTransfer(){}

		public void ExportScepiaXML(string fileName)
		{
			try
			{
				IUniDB UniDB = UniDBSingleton.Instance().UniDB;			
				
				DataSet DataUsers = new DataSet("USERS");
				DataUsers.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.Users_Diff"));
				DataUsers.Tables[0].TableName = "RECORD";

				DataSet DataSummons = new DataSet("SUMMONS");
				DataSummons.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.Summons_Diff"));
				DataSummons.Tables[0].TableName = "RECORD";

				DataSet DataEventsKind = new DataSet("EVENTKIND");
				DataEventsKind.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.InternetTransNodesDiff"));
				DataEventsKind.Tables[0].TableName = "RECORD";

				DataSet DataPlaces = new DataSet("PLACES");
				DataPlaces.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.InternetTransferPlacesDiff"));
				DataPlaces.Tables[0].TableName = "RECORD";

				DataSet DataEvents = new DataSet("EVENTS");
				DataEvents.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.InternetTransEventsDiff"));
				DataEvents.Tables[0].TableName = "RECORD";

				DataSet DataCandidacies = new DataSet("CANDIDACIES");
				DataCandidacies.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.InternetTransPathsDiff"));
				DataCandidacies.Tables[0].TableName = "RECORD";

				DataSet DataUserCandidacy = new DataSet("USERCANDIDACY");
				DataUserCandidacy.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.UsersCandidacy_Diff"));
				DataUserCandidacy.Tables[0].TableName = "RECORD";

				DataSet DataSurvey = new DataSet("SURVEYS");
				DataSurvey.Tables.Add(UniDB.RunStoredFunctionReturnDataTable("internet_data_transfer.SURVEYS_Diff"));
				DataSurvey.Tables[0].TableName = "RECORD";

				//insert new transfer date into InternetTransferDate - for next time delta.
				UniDB.RunStoredProcedure("internet_data_transfer.InsertNewDate");

				XmlTextWriter xwriter = new XmlTextWriter(fileName,System.Text.UnicodeEncoding.Unicode);
				xwriter.WriteStartDocument(true);
				xwriter.WriteStartElement("ITURDATA");
				DataUsers.WriteXml(xwriter);
				DataSummons.WriteXml(xwriter);
				DataEventsKind.WriteXml(xwriter);
				DataPlaces.WriteXml(xwriter);
				DataEvents.WriteXml(xwriter);
				DataCandidacies.WriteXml(xwriter);
				DataUserCandidacy.WriteXml(xwriter);
				DataSurvey.WriteXml(xwriter);
				xwriter.WriteEndElement();
				xwriter.Close();
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message,ex.InnerException);
			}
		}

		public void ImportScepiaXml(string filename)
		{
			Encoding enc = Encoding.GetEncoding(1255); 
			try 
			{
				string[] tables = new String[5]{"COURSECONFIRMATION" ,"CHANGEDEVENTS" ,"REQUESTS" ,"SUMMONCONFIRMATION","SURVEYS"};
				#region divert XML to five XMLs
				FileInfo fileInfo = new FileInfo(filename);
				if (!fileInfo.Exists) {throw new FileNotFoundException("File: " + filename + " not found!!!");}

				StreamReader sr = new StreamReader(filename,enc);
				string Allfile;
				string fileStart;

				Allfile = sr.ReadToEnd();
				sr.Close();
				fileStart = "<?xml version=\"1.0\" encoding=\"Windows-1255\"?><ITURDATA>";

				for (int i=0; i<5;i++)
				{
					StreamWriter sw = new StreamWriter(fileInfo.DirectoryName + "\\" + tables[i] + ".xml",false,enc);
					sw.WriteLine(fileStart);
					int intStart = Allfile.IndexOf("<"+tables[i]+">");
					if (intStart <=0)
					{
						sw.WriteLine("<"+tables[i]+" />");
						sw.WriteLine("</ITURDATA>");
					}
					else
					{
						int intEnd = Allfile.IndexOf("</"+tables[i]+">");
						if (intEnd > 0) 
						{
							sw.Write(Allfile.Substring(intStart,intEnd-intStart));
							sw.WriteLine("</"+tables[i]+">");
							sw.WriteLine("</ITURDATA>");
						}
					}	
					sw.Close();
				}

				#endregion
				#region import XML To DataSet and Update/import

				IUniDB UniDB = UniDBSingleton.Instance().UniDB;			
				
				#region "COURSECONFIRMATION"
				
				DataSet ds  = new DataSet("COURSECONFIRMATION");
				ds.ReadXml(fileInfo.DirectoryName + "\\" + "COURSECONFIRMATION" + ".xml");
				foreach(DataTable myTable in ds.Tables)
				{
					if (myTable.TableName =="RECORD")
					{
						foreach(DataRow myRow in myTable.Rows)
						{
							/*							@ID nvarchar(50),
														@UserID nvarchar(50),
														@UpdateDate datetime
														update candidate set OK=1, OKDate = @UpdateDate where RecordNumber = @UserID*/
						} 
					}
				}

				#endregion

				#region "CHANGEDEVENTS"
				ds  = new DataSet("CHANGEDEVENTS");
				ds.ReadXml(fileInfo.DirectoryName + "\\" + "CHANGEDEVENTS" + ".xml");
				foreach(DataTable myTable in ds.Tables)
				{
					if (myTable.TableName =="RECORD")
					{
						foreach(DataRow myRow in myTable.Rows)
						{

						}
					}
				}

				//if (ds.Tables.Count <= 1) continue;
				DataView eventsView = new DataView(ds.Tables[1]);
				eventsView.Sort = "USERID, UPDATEDATE DESC"; 
				eventsView.RowStateFilter = DataViewRowState.CurrentRows;

				string Userid = "";
				for(int j = 0; j<eventsView.Count;j++)
				{
					//the following row with conjunction with eventsView.Sort eliminate duoble user changes
					if (eventsView[j]["USERID"].ToString() != Userid)
					{
							
						ChangedEvent(Convert.ToInt32(eventsView[j]["USERID"]), Convert.ToInt32(eventsView[j]["OLDEVENT"]), Convert.ToInt32(eventsView[j]["NEWEVENT"]), Convert.ToDateTime(eventsView[j]["UPDATEDATE"].ToString()));
						Userid = eventsView[j]["USERID"].ToString();
					}
				}
				
				#endregion

				#region "REQUESTS"
				/*					DataSet ds  = new DataSet("REQUESTS");
										ds.ReadXml(fileInfo.DirectoryName + "\\" + "REQUESTS" + ".xml");
										foreach(DataTable myTable in ds.Tables)
										{
											if (myTable.TableName =="RECORD")
											{
												foreach(DataRow myRow in myTable.Rows)
												{
													/*	@ID numeric,
														@UserID numeric,
														@UpdateDate datetime,
														@Comments ntext
														declare @IDNumber numeric
														if @Comments is not null
														begin
															select @IDNumber = idnumber from candidate where RecordNumber = @UserID
													--		print 'id: ' + cast(@IDNumber as nvarchar(50))

															insert into InternetComments (IDNumber, Comment, CommentDate) values (@IDNumber, @Comments,@UpdateDate)
													--		print cast(@IDNumber as nvarchar(50)) + ' ' + cast(@UpdateDate as nvarchar(50)) + ' ' + cast( @Comments as nvarchar(50))
														end
												} 
											}
										}*/
				#endregion

				#region "SUMMONCONFIRMATION"
				ds  = new DataSet("SUMMONCONFIRMATION");
				ds.ReadXml(fileInfo.DirectoryName + "\\" + "SUMMONCONFIRMATION" + ".xml");
				foreach(DataTable myTable in ds.Tables)
				{
					if (myTable.TableName =="RECORD")
					{
						foreach(DataRow myRow in myTable.Rows)
						{
							EventArrivingConfirm(Convert.ToInt32(myRow["USERID"]), Convert.ToInt32(myRow["EVENTID"]), Convert.ToDateTime(myRow["UPDATEDATE"].ToString()));
						} 
					}
				}
							/*	@ID numeric,
	@UserID numeric,
	@EventID numeric,
	@UpdateDate datetime
)

AS
	declare @IDNumber numeric
	declare @Eventname nvarchar(50)

	if (@UpdateDate is not null) and (@EventID is not null) and (@UserID is not null)
	begin
		select @IDNumber = idnumber from candidate where RecordNumber = @UserID
		select @Eventname = name from events where id = @EventID
		update Summons set Arriving=1, ArrivingDate = @UpdateDate where idnumber = @IDNumber and Eventname = @Eventname
	end
--<ID>1</ID>   <USERID>757575242</USERID>   <EVENTID>1</EVENTID>   <UPDATEDATE>14/08/2002 15:07:44</UPDATEDATE> 
							 * */

				#endregion

				#region "SURVEYS"
				ds  = new DataSet("SUMMONCONFIRMATION");
				ds.ReadXml(fileInfo.DirectoryName + "\\" + "SUMMONCONFIRMATION" + ".xml");
				foreach(DataTable myTable in ds.Tables)
				{
					if (myTable.TableName =="RECORD")
					{
						foreach(DataRow myRow in myTable.Rows)
						{
							EventArrivingConfirm(Convert.ToInt32(myRow["USERID"]), Convert.ToInt32(myRow["EVENTID"]), Convert.ToDateTime(myRow["UPDATEDATE"].ToString()));
						} 
					}
				}

				/*					if (tables[i].ToString() == "SURVEYS")
										{
											if (ds.Tables.Count <= 1) continue;
					
											int InputCnt=0; //the FORM_INPUT row counter
											int FormInputRowCnt = ds.Tables[4].Rows.Count;
											//loop over the RECORD table	
											for(int j = 0; j<ds.Tables[1].Rows.Count;j++)
											{
												string Userid = ds.Tables[1].Rows[j][1].ToString();
												string SurveyID = ds.Tables[1].Rows[j][2].ToString();
												string UpdateDate = SQLDate(ds.Tables[1].Rows[j][7].ToString());
												bool IsToUpdate = RunSPreturnLastUpdate("InternetImportSURVEYS_UpdateDate",ConnectionString,Userid,SurveyID,UpdateDate);
												if (IsToUpdate==true)
													RunSQL("Exec InternetImportSURVEYS_DeleteDataBeforInsert '" + Userid + "', '" + SurveyID + "'",ConnectionString);
						
												//even if it is no need in update we will 
												 //loop over the SurveyValue Table for match the table
							 
					
												//loop over the SURVEYSVALUES table
												while (Userid==ds.Tables[4].Rows[InputCnt][2].ToString())
												{
													if (IsToUpdate==true) 
													{
														string Colname = ds.Tables[4].Rows[InputCnt][0].ToString();
														if (Colname == "3DMaxRank") 
															Colname = "ThreeDMaxRank";
														string Value = ds.Tables[4].Rows[InputCnt][3].ToString();
														if (Value.ToLower().Trim()=="on") Value = "1";
														if (Value.ToLower().Trim()=="off") Value = "0";
							
														RunSPwithParam("InternetImportSURVEYS",ConnectionString,Userid,SurveyID,Colname,Value);
													}
													InputCnt++;
													if (InputCnt == FormInputRowCnt) break;
												}
											}
											continue;
										}*/
				#endregion

				#endregion
			}
			catch (Exception ex)
			{
				//place to log the exception
				throw ex;
			}
		}

		private void ChangedEvent(int UserID, int OldEventId, int NewEventId, DateTime UpdateTime)
		{
			int CandidateId = 0;

			IUniDB UniDB = UniDBSingleton.Instance().UniDB;			
			clsParameter[] arrParam = new clsParameter[3];
			arrParam[0] = new clsParameter(false,UserID, OracleTypes.Int32);
			arrParam[1] = new clsParameter(false,OldEventId, OracleTypes.Int32);
			arrParam[2] = new clsParameter(true,CandidateId, OracleTypes.Int32); //out
						
			int NodeId = UniDB.RunStoredFunctionReturnInteger("Shraga_Node.Get_Node_By_Event", ref arrParam);

			Node CurrNode = (Node)DBObjectsObserver.Instance().GetDBObject(typeof(Node),NodeId,new object[]{NodeId});
			Event OldEvent = (Event)DBObjectsObserver.Instance().GetDBObject(typeof(Event),OldEventId,new object[]{OldEventId});
			Event NewEvent = (Event)DBObjectsObserver.Instance().GetDBObject(typeof(Event),NewEventId,new object[]{NewEventId});

			if (CurrNode.LastUpdate < UpdateTime)
			{
				if ((NewEvent.IsCanceled==false) && (NewEvent.IsFull(false)==false))
				{
					((CandidateNode)CurrNode.Candidates[CandidateId]).TrackUnassignment(EmptyData.EMPTY_NUMBER,NewEventId,4);
					CandidatesList cndl = new CandidatesList(CandidateId.ToString());
					((Event)CurrNode.Events[NewEventId]).AddCandidates(ref cndl, CurrNode,false);
				}
			}
		}

		private void EventArrivingConfirm(int UserID, int EventId, DateTime UpdateTime)
		{
			int CandidateId = 0;
			int NodeId = 0;

			IUniDB UniDB = UniDBSingleton.Instance().UniDB;			
			clsParameter[] arrParam = new clsParameter[3];
			arrParam[0] = new clsParameter(false,UserID, OracleTypes.Int32);
			arrParam[1] = new clsParameter(false,EventId, OracleTypes.Int32);
			arrParam[2] = new clsParameter(true,CandidateId, OracleTypes.Int32); //out
			arrParam[3] = new clsParameter(true,NodeId, OracleTypes.Int32); //out
						
			UniDB.RunStoredFunctionReturnInteger("Shraga_Node.Get_Node_By_Event", ref arrParam);

			CandidateNode cnd= (CandidateNode)DBObjectsObserver.Instance().GetDBObject(typeof(CandidateNode),NodeId,new object[]{NodeId});
			cnd.UpdateArriving(1);
		}

		private void InsertSurveyValue(int UserID, int SurveyKind, string FieldName, string Value)
		{
			//Get the Candidate Nodes contain the survey type
			int CandidateId = 0;
			int FieldId = 0;

			IUniDB UniDB = UniDBSingleton.Instance().UniDB;			
			clsParameter[] arrParam = new clsParameter[4];
			arrParam[0] = new clsParameter(false,UserID, OracleTypes.Int32);
			arrParam[1] = new clsParameter(false,SurveyKind, OracleTypes.Int32);
			arrParam[2] = new clsParameter(true,CandidateId, OracleTypes.Int32); //out
			arrParam[2] = new clsParameter(false,FieldName, OracleTypes.Int32); 
			arrParam[2] = new clsParameter(true,FieldId, OracleTypes.Int32); //out

			DataTable dtCandidateNode = UniDB.RunStoredFunctionReturnDataTable("Shraga_Survey.get_candidatenode_bysurveytype", ref arrParam);

			foreach (DataRow currDR in dtCandidateNode.Rows)
			{
				CandidateNode cnd= (CandidateNode)DBObjectsObserver.Instance().GetDBObject(typeof(CandidateNode),Convert.ToInt32(currDR[0]),new object[]{Convert.ToInt32(currDR[0])});
				cnd.ChangeSurveyFieldValue(FieldId,Value);
			}
		}
	}
}
